﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Model.Entities;

namespace Model
{
    public class UserProvider : DataAccess
    {
        public UserProvider()
        {
        }

        public UserDetails GetUserFromReader(IDataReader reader)
        {
            UserDetails user = new UserDetails((int)reader["UserID"], reader["UserName"].ToString(), 
                reader["Password"].ToString(), (UserRole) reader["UserRole"]);
            return user;
        }

        public bool GetUser(string usName, string pass)
        {
            SqlConnection con = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand("GetUser", con) {CommandType = CommandType.StoredProcedure};
            cmd.Parameters.Add("UserName", SqlDbType.NVarChar).Value = usName;
            cmd.Parameters.Add("Password", SqlDbType.NVarChar).Value = pass;
            con.Open();
            string name = (string) cmd.ExecuteScalar();
            con.Close();
            if (name != null)
                return true;
            return false;
        }

        public int GetUserRole(string usName)
        {
            SqlConnection con = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand("GetUserRole", con) { CommandType = CommandType.StoredProcedure };
            cmd.Parameters.Add("UserName", SqlDbType.NVarChar).Value = usName;
            con.Open();
            int userRole = (int) cmd.ExecuteScalar();
            con.Close();
            return userRole;
        }

        public List<UserDetails> GetAllUsers()
        {
            SqlConnection con = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand("GetUsers", con) {CommandType = CommandType.StoredProcedure};
            List<UserDetails> users = new List<UserDetails>();

            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader != null)
            {
                while (reader.Read())
                {
                    users.Add(GetUserFromReader(reader));
                }
                reader.Close();
            }
            con.Close();
            return users;
        }

        public UserDetails GetUserById(int id)
        {
            SqlConnection con = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand("GetUserById", con) {CommandType = CommandType.StoredProcedure};
            cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = id;
            UserDetails usr = new UserDetails();
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader != null)
            {
                while (reader.Read())
                {
                    usr = GetUserFromReader(reader);
                }
                reader.Close();
            }
            con.Close();
            return usr;
        }

        public void UpdateUser(UserDetails user)
        {
            SqlConnection con = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand("UpdateUser", con) { CommandType = CommandType.StoredProcedure };
            cmd.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = user.UserName;
            cmd.Parameters.Add("@Password", SqlDbType.NVarChar).Value = user.Password;
            cmd.Parameters.Add("@UserRole", SqlDbType.Int).Value = user.UserRole;
            cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = user.UserId;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public int InsertUser(UserDetails user)
        {
            SqlConnection con = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand("AddUser", con) { CommandType = CommandType.StoredProcedure };
            cmd.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = user.UserName;
            cmd.Parameters.Add("@Password", SqlDbType.NVarChar).Value = user.Password;
            cmd.Parameters.Add("@UserRole", SqlDbType.Int).Value = user.UserRole;
            cmd.Parameters.Add("@UserID", SqlDbType.Int).Direction = ParameterDirection.Output;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            return (int) cmd.Parameters["@UserID"].Value;
        }

        public void DeleteUser(int id)
        {
            SqlConnection con = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand("DeleteUser", con) { CommandType = CommandType.StoredProcedure };
            cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = id;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}
